using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Entity;

using System.Collections.Specialized;
using System.Data;
using Entity;
namespace DAL
{
    public class SysUserDAL
    {
        /// <summary>
        /// 配置单件实例
        /// </summary>
        public static readonly SysUserDAL Instance = new SysUserDAL();
        
        /// <summary>   
        /// 查询管理员账号
        /// </summary>
        public int SelectUserCount(string where)
        {
            int recordCount = 0;
            string sql = "select count(id) from Sys_User";

            if (!string.IsNullOrEmpty(where))
            {
                sql += " where " + where;
            }
            try
            {
                recordCount = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql));
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return recordCount;
        }
        /// <summary>
        /// 查询管理员账号
        /// </summary>
        public List<SysUser> SelectUserInfo(int pageSize, int pageIndex, string where)
        {
            SysUser user = null;
            List<SysUser> lstUser = new List<SysUser>();
            SqlDataReader sdr = null;
            string sql = "select * from (select id,login_name,pass_word,state,user_name,row_number() over (order by id desc ) as pageId from [Sys_User]  ";
            if (!string.IsNullOrEmpty(where))
            {
                sql += " where " + where;
            }
            sql += ") as t where pageId between (" + pageIndex + "-1)*" + pageSize + " +1 and " + pageIndex + "*" + pageSize;
            try
            {

                using (sdr =
                    SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction,
                    System.Data.CommandType.Text, sql, null))
                {
                    while (sdr.Read())
                    {
                        user = new SysUser()
                        {
                           ID = sdr.GetInt32(0),
                            Login_name = sdr.GetString(1),
                           Pass_word = ClassMD5.DecryptString(sdr.GetString(2)),
                           State = sdr.GetString(3),
                            User_name = sdr.IsDBNull(4) ? "" : sdr.GetString(4)
                        };
                        lstUser.Add(user);
                    }
                }
            }
            catch { }
            finally
            {
                if (sdr != null && !sdr.IsClosed)
                    sdr.Close();
            }
            return lstUser;
        }
        /// <summary>
        /// 新增系统用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int InsertSysUser(SysUser user)
        {
            int count=0;
            string sql = "insert into sys_user(login_name,pass_word,user_name,phone,email,remark,state) values(@login_name,@pass_word,@user_name,@phone,@email,@remark,'1') ";
            try
            {
                SqlParameter[] para = {
                    new SqlParameter("@login_name",SqlDbType.VarChar),
				    new SqlParameter("@pass_word",SqlDbType.VarChar),
				    new SqlParameter("@user_name",SqlDbType.VarChar),
				    new SqlParameter("@phone",SqlDbType.VarChar),
				    new SqlParameter("@email",SqlDbType.VarChar),
				    new SqlParameter("@remark",SqlDbType.VarChar)
                    
                };
                para[0].Value = user.Login_name;
                para[1].Value = user.Pass_word;
                para[2].Value = user.User_name;
                para[3].Value = user.Phone;
                para[4].Value = user.Email;
                para[5].Value = user.Remark;
                count = SqlHelper.ExecuteNonQuery(sql, para);
            }
            catch (Exception err) {  }
            return count;
        }
       

        /// <summary>
        /// 启用或停用账号
        /// </summary>
        /// <param name="id"></param>
        /// <param name="user_status">0:禁用 1:启用</param>
        /// <returns></returns>
        public Boolean setUserDisabled(string id, string user_status)
        {
            int recordCount = 0;
            string sql = "update Sys_User set state='" + user_status + "' where id=" + id;
            try
            {
                recordCount = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, null);
                if (recordCount > 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }

        /// <summary>
        /// 重置用户密码：666666
        /// </summary>
        public Boolean RePassWord(string id)
        {
            string password = ClassMD5.EncryptString("666666");
            int recordCount = 0;
            string sql = "update Sys_User set pass_word='" + password + "' where id=" + id;
            try
            {
                recordCount = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, null);
                if (recordCount > 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }

        /// <summary>
        /// 修改登录时间，登录IP
        /// </summary>
        public Boolean UpdateLoingInfo(string id,string ip)
        {
            int recordCount = 0;
            string sql = "update Sys_User set login_ip='" + ip + "',last_login_time='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' where id=" + id;
            try
            {
                recordCount = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, null);
                if (recordCount > 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }



        /// <summary>
        /// 查询管理员账号
        /// </summary>
        public List<SysUser> CheckUserLogin(string loginName)
        {
            SysUser user = null;
            List<SysUser> lstUser = new List<SysUser>();
            SqlDataReader sdr = null;
            string sql = "select id,login_name,pass_word,user_name from [Sys_User]   ";
            sql += " where login_name='" + loginName + "' and state='1'";
           
            try
            {
                using (sdr =
                    SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction,
                    System.Data.CommandType.Text, sql, null))
                {
                    while (sdr.Read())
                    {
                        user = new SysUser()
                        {
                            ID = sdr.GetInt32(0),
                            Login_name = sdr.GetString(1),
                            Pass_word = sdr.GetString(2),
                            User_name = sdr.IsDBNull(3) ? "" : sdr.GetString(3)
                        };
                        lstUser.Add(user);
                    }
                }
            }
            catch { }
            finally
            {
                if (sdr != null && !sdr.IsClosed)
                    sdr.Close();
            }
            return lstUser;
        }
    }
}
